if exists(select 1
		  from sysobjects
		  where name = 'Vacancies_TypeHistory')
  drop table Vacancies_TypeHistory
go

create table Vacancies_TypeHistory
(
  Id int identity(1,1) not null,
  VacancyId int not null,
  [Date] date not null,
  VacancyTypeId int not null,
)  
go

alter table Vacancies_TypeHistory add constraint PK_Vacancies_TypeHistory_ID primary key(Id) 
go

alter table Vacancies_TypeHistory add constraint FK_Vacancies_TypeHistory_Vacancies_VacancyId 
      foreign key (VacancyId) references Vacancies(Id) on delete cascade
go

alter table Vacancies_TypeHistory add constraint FK_Vacancies_TypeHistory_Candidate_Types_CandidateTypeId 
      foreign key (VacancyTypeId) references Vacancy_Types(Id) on delete cascade
go

insert into Vacancies_TypeHistory(VacancyId, [Date], VacancyTypeId)		
select Id, isnull(cast(DLC as date),getdate()), VacancyTypeId
from Vacancies
go

ALTER TRIGGER [dbo].[Vacancies_ITrig] ON [dbo].[Vacancies] AFTER Insert
AS 
BEGIN
  if @@rowcount = 0 return	 

  insert into Vacancies_TypeHistory(VacancyId, [Date], VacancyTypeId)		
  select Id, cast(getdate() as date), VacancyTypeId
  from inserted i

  update v set
    v.DLC = convert(datetime,convert(varchar,getdate(),102))
  from Vacancies v
  join inserted i on v.Id = i.Id
end

GO



ALTER TRIGGER [dbo].[Vacancies_UTrig] ON [dbo].[Vacancies] AFTER UPDATE
AS 
BEGIN
  if @@rowcount = 0 return	 
  
  insert into Vacancies_TypeHistory(VacancyId, [Date], VacancyTypeId)		
  select i.Id, cast(getdate() as date), i.VacancyTypeId
  from inserted i
  join deleted d on d.Id = i.Id
  where isnull(i.VacancyTypeId,0) <> isnull(d.VacancyTypeId,0)

  update v set
    v.DLC = convert(datetime,convert(varchar,getdate(),102))
  from Vacancies v
  join inserted i on v.Id = i.Id
  join deleted d on d.Id = i.Id
  where (isnull(i.EmployerID,0) <> isnull(d.EmployerID,0) or
		 isnull(i.PositionID,0) <> isnull(d.PositionID,0) or
		 isnull(i.EmployeeWhoFindCandidatesID,0) <> isnull(d.EmployeeWhoFindCandidatesID,0) or
		 isnull(i.EmployeeWhoApproveCandidatesID,0) <> isnull(d.EmployeeWhoApproveCandidatesID,0) or
		 isnull(i.SalaryRangeID,0) <> isnull(d.SalaryRangeID,0) or
		 isnull(i.AgeMin,0) <> isnull(d.AgeMin,0) or
		 isnull(i.AgeMax,0) <> isnull(d.AgeMax,0) or
		 isnull(i.CarID,0) <> isnull(d.CarID,0) or
		 isnull(i.VacancyTypeId,0) <> isnull(d.VacancyTypeId,0) or
		 isnull(i.SexID,0) <> isnull(d.SexID,0) or
		 isnull(i.Car_CompareImportantID,0) <> isnull(d.Car_CompareImportantID,0) or
		 isnull(i.Sex_CompareImportantID,0) <> isnull(d.Sex_CompareImportantID,0) or
		 isnull(i.Age_CompareImportantID,0) <> isnull(d.Age_CompareImportantID,0) or
		 isnull(i.Position_CompareImportantID,0) <> isnull(d.Position_CompareImportantID,0) or
		 isnull(i.ExpirienceYear_CompareImportantID,0) <> isnull(d.ExpirienceYear_CompareImportantID,0) or
		 isnull(d.VacancyDate,'1 jan 2000') <> isnull(i.VacancyDate,'1 jan 2000') or
		 isnull(d.SalaryDate,'1 jan 2000') <> isnull(i.SalaryDate,'1 jan 2000') or
		 isnull(d.TrialPeriod,'1 jan 2000') <> isnull(i.TrialPeriod,'1 jan 2000') or
		 isnull(d.TradeMarks,'') <> isnull(i.TradeMarks,'') or
		 isnull(d.EducationType,'') <> isnull(i.EducationType,'') or
		 isnull(d.SalaryOnTrialPeriod,'') <> isnull(i.SalaryOnTrialPeriod,'') or
		 isnull(d.Education,'') <> isnull(i.Education,'') or
		 update(Comment) or Update(Comment1) or update(CommentForHistory) or
		 isnull(d.ExpirienceYear,0) <> isnull(i.ExpirienceYear,0)) and
		isnull(v.DLC,dateadd(day,-1,getdate())) <> convert(datetime,convert(varchar,getdate(),102))

END
GO
